# -*- coding: utf-8 -*-
'''
Created on 2013-4-10
@author: CL.Lam
'''
from datetime import datetime as dt

from flask.helpers import url_for
from sqlalchemy import Column
from sqlalchemy.types import Integer, Text, DateTime, Numeric
from sqlalchemy.schema import ForeignKey
from sqlalchemy.orm import backref, relation, synonym
from sqlalchemy.sql.expression import and_, func
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.ext.declarative import declared_attr

from interface import SysMixin
# from system import SysFile
from sys2do.model import db, DeclarativeBase, qry, SysFile, SysDict
from sys2do.util.sa_helper import ListFieldColumn, genNo, nextVal
from sys2do.model.interface import DBMixin, TreeMixin
from sys2do.util.decorator import trycatch
from sys2do.util.logic_helper import getPermission, getCurrentShopID, \
    getCurrentShopProfile
from sys2do.constant import SHOP_TYPE_MAIN, PRODUCT_VALID, PRODUCT_INVALID, \
    ACTIVE, PRODUCT_SALEABLE, ITEM_AVAILABLE
from sys2do.setting import NO_IMAGE_URL
from sys2do.util.barcode_helper import genBarcodeFile




__all__ = ['Province', 'City', 'Bank', 'Payterm', 'Paytype',
           'InventoryLocation', 'Supplier', 'Customer', 'ProductType', 'ProductShap',
           'ProductSeries', 'Product', 'Item', 'Shop', 'ShopProfile', 'Member',
           'Currency', 'CurrencyRatio', 'FinAccount',
           ]



class Province( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_province'

    id = Column( Integer, autoincrement = True, primary_key = True )
    name = Column( Text )
    code = Column( Text )


    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name


    def children( self ):
        return qry( City ).filter( and_( City.parent_code == self.code, City.active == 0 ) ).order_by( City.name ).all()

    @classmethod
    def _get_fields( clz ):
        return ['name', 'code']

    def editable( self ):   return getPermission( 'MASTER_EDIT' )
    def deletable( self ): return getPermission( 'MASTER_DEL' )


class City( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_city'

    id = Column( Integer, autoincrement = True, primary_key = True )
    name = Column( Text )
    code = Column( Text )
    parent_code = Column( Text )


    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def parent( self ):  return qry( Province ).filter( Province.code == self.parent_code ).one()


    @classmethod
    def _get_fields( clz ):
        return ['name', 'code', 'parent_code']

    def editable( self ):   return getPermission( 'MASTER_EDIT' )
    def deletable( self ): return getPermission( 'MASTER_DEL' )



class Bank( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_bank'

    id = Column( Integer, autoincrement = True, primary_key = True )
    name = Column( Text, unique = True, )

    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'MASTER_EDIT' )
    def deletable( self ): return getPermission( 'MASTER_DEL' )



class Payterm( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_payterm'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_payterm_seq', '010' ) )
    name = Column( 'name', Text, doc = u'名称' )
    desc = Column( 'desc', Text, doc = u'描述' )
    isDefault = Column( 'is_default', Integer, default = 0 )    # 0 is not default, 1 is default

    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'MASTER_EDIT' )
    def deletable( self ): return getPermission( 'MASTER_DEL' )




class Paytype( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_paytype'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_paytype_seq', '011' ) )
    name = Column( 'name', Text, doc = u'名称', unique = True, )
    desc = Column( 'desc', Text, doc = u'描述' )
    isDefault = Column( 'is_default', Integer, default = 0 )    # 0 is not default, 1 is default

    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'MASTER_EDIT' )
    def deletable( self ): return getPermission( 'MASTER_DEL' )





class InventoryLocation( DeclarativeBase, SysMixin, DBMixin, TreeMixin ):
    __tablename__ = 'master_inventory_location'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_ivt_seq', '012' ) )
    name = Column( 'name', Text, doc = u'名称' )
    manager = Column( 'manager', Text, doc = u'管理员' )
    provinceID = Column( 'province_id', Integer, ForeignKey( 'master_province.id' ) )
    province = relation( Province )
    cityID = Column( 'city_id', Integer, ForeignKey( 'master_city.id' ) )
    city = relation( City )
    address = Column( 'address', Text, doc = u'地址' )
    remark = Column( 'remark', Text, doc = u'备注' )
    fullPath = Column( 'full_path', Text, doc = u'全路径' )
    fullPathIDs = Column( 'full_path_ids', Text )
    parentID = Column( 'parent_id', Integer, ForeignKey( 'master_inventory_location.id' ) )
    parent = relation( 'InventoryLocation', backref = backref( "children" ), remote_side = 'InventoryLocation.id' )
    referID = Column( 'refer_id', Integer, default = None )    # refer to the shop
    type = Column( 'type', Integer, default = 1, doc = u'仓位类型' )    # 0 is for system used ,1 is for user cureate

    products = relation( "InventoryProduct", backref = "inventoryLocation" )


    def __str__( self ): return self.fullPath
    def __repr__( self ): return self.fullPath
    def __unicode__( self ): return self.fullPath

    @property
    def getLevel( self ):
        return len( self.fullPathIDs.split( '|' ) )

    @classmethod
    def listTree( clz, parentID = None, includeSelf = False ):
        data = []
        if parentID:
            data = clz.get( parentID ).getTree( [clz.active == ACTIVE] )
        else:
            data = clz.findTree( [clz.referID == getCurrentShopID(), clz.active == ACTIVE] )
        return data

    def getChildren( self ):
        clz = self.__class__
        return qry( self.__class__ ).filter( and_( clz.active == ACTIVE, clz.fullPathIDs.like( '%s|%%' % self.fullPathIDs ) ) ).order_by( clz.fullPathIDs )

    @classmethod
    def getInTravelIvt( clz ):  return clz._getIvtByName( 'IN_TRAVEL' )

    @classmethod
    def getSoldOutIvt( clz ):   return clz._getIvtByName( 'SOLD_OUT' )

    @classmethod
    def getPurchaseIvt( clz ):   return clz._getIvtByName( 'PURCHASE' )

    @classmethod
    def getWastageIvt( clz ):   return clz._getIvtByName( 'WASTAGE' )

    @classmethod
    def getProcessIvt( clz ):   return clz._getIvtByName( 'PROCESS' )

    @classmethod
    def _getIvtByName( clz, name ):
        return qry( clz ).filter( and_( clz.active == ACTIVE, clz.name == name ) ).one()

    def editable( self ):   return getPermission( 'INVENTORY_EDIT' )
    def deletable( self ):
        return self.type == 1 and getPermission( 'INVENTORY_DEL' )


class Supplier( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'master_supplier'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_spl_seq', '013' ) )
    name = Column( 'name', Text, doc = u'供应商简称' )
    fullName = Column( 'full_name', Text, doc = u'供应商全称' )
    provinceID = Column( 'province_id', Integer, ForeignKey( 'master_province.id' ), doc = u'省' )
    province = relation( Province )
    cityID = Column( 'city_id', Integer, ForeignKey( 'master_city.id' ), doc = u'市' )
    city = relation( City )
    address = Column( 'address', Text, doc = u'地址' )
    tel = Column( 'tel', Text, doc = u'联系电话' )
    att = Column( 'att', Text, doc = u'联系人' )
    mobile = Column( 'mobile', Text, doc = u'手机' )

    paytermID = Column( 'payterm_id', Integer, ForeignKey( 'master_payterm.id' ), doc = u'结算方式' )
    payterm = relation( Payterm )

    remark = Column( 'remark', Text, doc = u'备注' )


    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'SUPPLIER_EDIT' )
    def deletable( self ): return getPermission( 'SUPPLIER_DEL' )



class Customer( DeclarativeBase, SysMixin ):

    __tablename__ = 'master_customer'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_cst_seq', '014' ) )
    name = Column( 'name', Text, doc = u'名称' )
    tel = Column( 'tel', Text, doc = u'联系电话' )
    mobile = Column( 'mobile', Text, doc = u'手机' )
    provinceID = Column( 'province_id', Integer, ForeignKey( 'master_province.id' ) )
    province = relation( Province )
    cityID = Column( 'city_id', Integer, ForeignKey( 'master_city.id' ) )
    city = relation( City )
    address = Column( 'address', Text, doc = u'地址' )

    refer1 = Column( 'refer1', Text )
    refer2 = Column( 'refer2', Text )
    refer3 = Column( 'refer3', Text )
    refer4 = Column( 'refer4', Text )
    refer5 = Column( 'refer5', Text )



class ProductType( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_product_type'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_pdt_type_seq', '015', 3 ) )
    name = Column( 'name', Text, doc = u'名称', unique = True, )
#     prefixCode = Column( 'prefix_code', Text, doc = u'商品编号前缀' )
    isDefault = Column( 'is_default', Integer, default = 0 )    # 0 is not default, 1 is default

    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'MASTER_EDIT' )
    def deletable( self ): return getPermission( 'MASTER_DEL' )

    @property
    def prefixCode( self ): return self.no[-3:]


class ProductShap( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_product_shap'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_pdt_type_seq', '016' ) )
    name = Column( 'name', Text, doc = u'名称', unique = True, )
    isDefault = Column( 'is_default', Integer, default = 0 )    # 0 is not default, 1 is default

    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'MASTER_EDIT' )
    def deletable( self ): return getPermission( 'MASTER_DEL' )


class ProductSeries( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_product_series'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_pdt_series_seq', '017' ) )
    name = Column( 'name', Text, doc = u'名称', unique = True, )
    isDefault = Column( 'is_default', Integer, default = 0 )    # 0 is not default, 1 is default

    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'MASTER_EDIT' )
    def deletable( self ): return getPermission( 'MASTER_DEL' )




class Product( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'master_product'

    id = Column( Integer, autoincrement = True, primary_key = True )
#     no = Column( 'no', Text, default = genNo( 'zb_pdt_seq', '108' ), doc = u'系统编号' )
    no = Column( 'no', Text, doc = u'系统编号' )
    barcode = Column( 'barcode', Text, doc = u'条码值' )
#     epc = Column( 'epc', Text, doc = u'EPC' )
    name = Column( 'name', Text, doc = u'商品名称' )
    typeID = Column( 'type_id', Integer, ForeignKey( 'master_product_type.id' ), doc = u'商品类型' )
    type = relation( ProductType )
    seriesID = Column( 'series_id', Integer, ForeignKey( 'master_product_series.id' ), doc = u'商品系列' )
    series = relation( ProductSeries )
    shapID = Column( 'shap_id', Integer, ForeignKey( 'master_product_shap.id' ), doc = u'商品形状' )
    shap = relation( ProductShap )

    styleNo = Column( 'style_no', Text, doc = u'款号' )
    weight = Column( 'weight', Text, doc = u'重量' )
    length = Column( 'length', Text, doc = u'手寸长度' )
    clarity = Column( 'clarity', Text, doc = u'净度' )
    color = Column( 'color', Text, doc = u'颜色' )
    goldFineness = Column( 'gold_fineness', Text, doc = u'金成色' )
    certificate = Column( 'certificate', Text, doc = u'证书号' )
    length = Column( 'length', Text, doc = u'长度' )
    width = Column( 'width', Text, doc = u'宽度' )
    height = Column( 'height', Text, doc = u'高度' )

    pPrice = Column( 'purchase_price', Numeric( 15, 2 ), default = 0, doc = u'建议采购价格' )
    sPrice = Column( 'sale_price', Numeric( 15, 2 ), default = 0, doc = u'建议销售价格' )
    oldsPrice = Column( 'old_sale_price', Numeric( 15, 2 ), default = 0, doc = u'旧价格' )
    fee = Column( 'fee', Numeric( 15, 2 ), default = 0, doc = u'加工费用' )
    desc = Column( 'desc', Text, doc = u'描述' )

    thumbID = Column( 'thumb_id', Integer, ForeignKey( 'system_upload_file.id' ), doc = u'缩略图 ' )
    thumb = relation( SysFile, primaryjoin = thumbID == SysFile.id )

    realImgID = Column( 'real_img_id', Integer, ForeignKey( 'system_upload_file.id' ), doc = u'浏览图 ' )
    realImg = relation( SysFile, primaryjoin = realImgID == SysFile.id )

    barcodeImgID = Column( 'barcode_img_id', Integer, ForeignKey( 'system_upload_file.id' ), )
    barcodeImg = relation( SysFile , primaryjoin = barcodeImgID == SysFile.id )

    _image = Column( 'image', Text )
    isDeploy = Column( 'is_deploy', Integer, default = 0 )    # 0 is not deploy to zhubao website, 1 is show outside
    itemSeq = Column( Integer, default = 0 )

    ivtPdts = relation( "InventoryProduct" )

    @property
    def shopIvtPdts( self ):
        currentSP = ShopProfile.get( getCurrentShopID() )
        spIvtIDs = [i.id for i in currentSP.getIvts()]
        results = []
        for i in self.ivtPdts:
            if i.ivtID in spIvtIDs:
                results.append( i )
        return results

#     def assignNo( self ):
    def __init__( self, *args, **kwargs ):
        super( self.__class__, self ).__init__( *args, **kwargs )
        d = qry( SysDict ).filter( and_( SysDict.type == 'OBJECT_PREFIX', SysDict.name == self.__class__.__name__ ) ).one()
        seq = nextVal( 'zb_pdt_seq' )
        typePrefix = '00'
        if self.typeID :
            t = qry( ProductType ).get( self.typeID )
            if t : typePrefix = t.prefixCode
        self.no = '%s%s%s%.5d' % ( d.value, dt.now().strftime( "%y%m%d" ), typePrefix, seq )

    @property
    def thumbUrl( self ):
        return self.thumb.url if self.thumb else NO_IMAGE_URL

    @trycatch( [] )
    def _getImage( self ):
#         from system import SysFile
        ids = filter( bool, self._image.split( "|" ) )
        if not ids : return []
        return qry( SysFile ).filter( and_( SysFile.active == 0, SysFile.id.in_( ids ) ) ).order_by( SysFile.id )

    def _setImage( self, v ):
        ids = None
        if v :
            if type( v ) == list:
                ids = "|".join( map( unicode, v ) )
            elif isinstance( v, basestring ):
                ids = v
        self._image = ids

    @declared_attr
    def image( self ):
        return synonym( '_image', descriptor = property( self._getImage, self._setImage ) )


    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name


    def belongToShop( self ):
        from logic import InventoryProduct
        result = qry( Shop ).filter( and_( InventoryProduct.active == 0, Shop.active == 0, InventoryLocation.active == 0,
                                          InventoryProduct.pdtID == self.id,
                                          InventoryProduct.ivtID == InventoryLocation.id,
                                          InventoryLocation.referID == Shop.id ) ).all()
        return [r.id for r in result]


    def editable( self ):
        from sys2do.model import InventoryProduct
        if not getPermission( 'PRODUCT_EDIT' ) : return False
        c = qry( func.count( InventoryProduct.pdtID ) ).filter( InventoryProduct.pdtID == self.id ).scalar()
        if c < 1 : return True    # the product have not enter the inventory yet

        spobj = getCurrentShopProfile()
        if spobj.shopType != SHOP_TYPE_MAIN : return False
        if spobj.shopID not in self.belongToShop() : return False
        return True


    def deletable( self ):
#         if self.isValid != PRODUCT_INVALID : return False
        if not getPermission( 'PRODUCT_DEL' ) : return False
        return True


    def makeDesc( self ):
        params = []
        colNames = ['no', 'barcode', 'styleNo', 'weight', 'length', 'width', 'height', 'clarity',
                    'color', 'goldFineness', 'certificate', 'desc']
        m = self.__mapper__.columns
        for colName in colNames:
            if colName not in m : continue
            colClz = m[colName]
            val = getattr( self, colName )
            if val : params.append( "%s : %s" % ( colClz.doc, val ) )

        if self.typeID : params.append( "%s : %s" % ( u'商品类型', self.type ) )
        if self.seriesID : params.append( "%s : %s" % ( u'商品系列', self.series ) )
        if self.shapID : params.append( "%s : %s" % ( u'商品形状', self.shap ) )

        return " , ".join( params )

    def toJson( self, * args ):
        d = super( Product, self ).toJson( *args )
        d['type'] = unicode( self.type ) if self.typeID else ''
        d['shap'] = unicode( self.shap ) if self.shapID else ''
        d['series'] = unicode( self.series ) if self.seriesID else ''
        return d


    def genItem( self, qty ):
        if qty <= 0 : return []
        obj = qry( self.__class__ ).filter( self.__class__.id == self.id ).with_lockmode( 'update' ).one()
        start , obj.itemSeq = obj.itemSeq, obj.itemSeq + qty
        items = [Item( pdtID = self.id, no = '%s%s' % ( self.no, start + x + 1 ) ) for x in range( qty )]
        db.add_all( items )
        return items


class Item( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'master_item'

    id = Column( Integer, autoincrement = True, primary_key = True )

    pdtID = Column( 'pdt_id', Integer, ForeignKey( 'master_product.id' ), doc = u'商品' )
    pdt = relation( Product, primaryjoin = pdtID == Product.id )

    no = Column( 'no', Text , doc = u'系统编号' )
    epc = Column( 'epc', Text, doc = u'EPC' )
    pPrice = Column( 'purchase_price', Numeric( 15, 2 ), default = 0, doc = u'最新采购价格' )
    sPrice = Column( 'sale_price', Numeric( 15, 2 ), default = 0, doc = u'最新销售价格' )

    ivtID = Column( 'ivt_id', Integer, ForeignKey( 'master_inventory_location.id' ) )
    ivt = relation( InventoryLocation )

    status = Column( 'status', Integer, default = ITEM_AVAILABLE )    # 0 is available , 1 is locked
    refer = Column( 'refer', Text , doc = u'涉及' )


    def __str__( self ): return self.no
    def __repr__( self ): return self.no
    def __unicode__( self ): return self.no



class Shop( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'master_shop'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_shop_seq', '018' ) )
    code = Column( 'code', Text, doc = u'店铺代码' )
    shortName = Column( 'short_name', Text, doc = u'店铺简称' )
    name = Column( 'name', Text , doc = u'店铺全称' )
    provinceID = Column( 'province_id', Integer, ForeignKey( 'master_province.id' ), doc = u'省' )
    province = relation( Province )
    cityID = Column( 'city_id', Integer, ForeignKey( 'master_city.id' ), doc = u'市' )
    city = relation( City )
    address = Column( 'address', Text , doc = u'店铺地址' )
    fund = Column( 'fund', Text , doc = u'注册资金' )
    legalPerson = Column( 'legal_person', Text , doc = u'注册法人' )


    def __str__( self ): return self.shortName
    def __repr__( self ): return self.shortName
    def __unicode__( self ): return self.shortName

    def editable( self ):   return getPermission( 'SHOP_EDIT' )
    def deletable( self ): return getPermission( 'SHOP_DEL' )

    def getProfile( self ):
        return qry( ShopProfile ).filter( and_( ShopProfile.active == 0, ShopProfile.shopID == self.id ) ).one()


class ShopProfile( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_shop_profile'

    id = Column( Integer, autoincrement = True, primary_key = True )

    no = Column( 'no', Text, default = genNo( 'zb_shop_profile_seq', '100' ) )
    shopID = Column( 'shop_id', Integer, ForeignKey( 'master_shop.id' ), )
    shop = relation( Shop, backref = 'shopProfile' )

    shopType = Column( 'shop_type', Integer, doc = u'店铺类型' )    # 0 is for main shop, 1 is for branch shop
    saleType = Column( 'sale_type', Integer , doc = u'销售类型' )    # 0 is for self-sale mode, 1 is for join-sale mode
    inventoryID = Column( 'inventory_id', Integer, ForeignKey( 'master_inventory_location.id' ), )
    inventory = relation( InventoryLocation )

    canSale = Column( 'can_sale', Integer, default = 0, doc = u'是否有销售功能' )    # 0 is for sale, 1 is not for sale
    canPurchase = Column( 'can_purchase', Integer, default = 0, doc = u'是否有采购功能' )    # 0 is for purchase, 1 is not for purchase
    canGetWarehouse = Column( 'can_get_warehouse', Integer, default = 0, doc = u'是否有仓存功能' )    # 0 is own warehouse, 1 is not warehouse
    canUpdateProduct = Column( 'can_update_product', Integer, default = 0, doc = u'是否可以更新商品信息' )    # 0 means the shop could update the product info, 1 means not
    canUpdateOfficer = Column( 'can_update_officer', Integer, default = 0, doc = u'是否可以更新人员信息' )    # 0 means the shop could update the officer, 1 means not

    _groupIDs = Column( 'group_ids', Text )

    @trycatch( [] )
    def _getGroupIDs( self ):
        return self._groupIDs.split( "|" )

    def _setGroupIDs( self, v ):
        ids = None
        if v :
            if type( v ) == list:
                ids = "|".join( map( unicode, v ) )
            elif isinstance( v, basestring ):
                ids = v
        self._groupIDs = ids


    @declared_attr
    def groupIDs( self ):
        return synonym( '_groupIDs', descriptor = property( self._getGroupIDs, self._setGroupIDs ) )

    def getIvts( self ):
        result = qry( InventoryLocation ).filter( and_( InventoryLocation.active == ACTIVE,
                                           InventoryLocation.referID == self.shopID ) ).order_by( InventoryLocation.fullPath )
        return result


class Member( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_member'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_member_seq', '111' ) )
    name = Column( 'name', Text, doc = u'会员名称' )
    tel = Column( 'tel', Text, doc = u'联系电话' )
    mobile = Column( 'mobile', Text, doc = u'手机' )
    provinceID = Column( 'province_id', Integer, ForeignKey( 'master_province.id' ) )
    province = relation( Province )
    cityID = Column( 'city_id', Integer, ForeignKey( 'master_city.id' ) )
    city = relation( City )
    address = Column( 'address', Text, doc = u'地址' )
    shopID = Column( 'shop_id', Integer, ForeignKey( 'master_shop.id' ), )
    shop = relation( Shop )
    joinTime = Column( 'join_time', DateTime, default = dt.now, doc = u'加入时间' )

    idCard = Column( 'id_card', Text, doc = u'身份证' )
    discounts = Column( 'discounts', Numeric( 15, 2 ), default = 1, doc = u'折扣' )

    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'MEMBER_EDIT' )
    def deletable( self ): return getPermission( 'MEMBER_DEL' )



class Currency( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_currency'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_currency_seq', '020' ) )

    code = Column( 'code', Text, doc = u'货币代码', unique = True, )
    name = Column( 'name', Text, doc = u'名称' )
    isDefault = Column( 'is_default', Integer, default = 0 )    # 0 is not default, 1 is default
    ratio = Column( 'ratio', Numeric( 15, 2 ), doc = u'与美元对换率' )
#     oldRatio = Column( 'old_ratio', Numeric( 15, 2 ) )
#     effTime = Column( 'eff_time', DateTime, default = dt.now, doc = u'生效时间' )

    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'MASTER_EDIT' )    # TO BE UPDATE
    def deletable( self ): return getPermission( 'MASTER_DEL' )    # TO BE UPDATE

    def viewURL( self ): return url_for( "bpCry.view", action = "view", id = self.id )

    @classmethod
    def getRatio( clz, code, time = None ):
        if not time : time = dt.now()
        obj = qry( CurrencyRatio ).filter( and_( 
                                       clz.active == 0, clz.code == code, clz.id == CurrencyRatio.currencyID,
                                       time >= CurrencyRatio.effTime
                                       ) ).first()
        return obj.ratio





class CurrencyRatio( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_currency_ratio'

    id = Column( Integer, autoincrement = True, primary_key = True )

    currencyID = Column( 'currency_id', Integer, ForeignKey( 'master_currency.id' ), )
    currency = relation( Currency , backref = backref( 'ratios', order_by = 'desc(CurrencyRatio.effTime)' ), )

    ratio = Column( 'ratio', Numeric( 15, 2 ), doc = u'与美元汇率' )
    effTime = Column( 'eff_time', DateTime, default = dt.now, doc = u'生效时间' )



class FinAccount( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'master_fin_account'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_fin_account_seq', '021' ) )
    name = Column( 'name', Text, doc = u'名称' )

    shopProfileID = Column( 'shop_profile_id', Integer, ForeignKey( 'master_shop_profile.id' ), )
    shopProfile = relation( ShopProfile , backref = 'accounts' )

    bankID = Column( 'bank_id', Integer, ForeignKey( 'master_bank.id' ), )
    bank = relation( Bank , backref = 'accounts' )

    currencyID = Column( 'currency_id', Integer, ForeignKey( 'master_currency.id' ), doc = u'账户货币' )
    currency = relation( Currency )

    paytypeID = Column( 'paytype_id', Integer, ForeignKey( 'master_paytype.id' ), doc = u'支付方式' )
    paytype = relation( Paytype )

    code = Column( 'code', Text, doc = u'账户号码' )
    owner = Column( 'owner', Text, doc = u'账户拥有人' )
    amount = Column( 'amount', Numeric( 15, 2 ), default = 0, doc = u'金额' )

    def __str__( self ): return self.name
    def __repr__( self ): return self.name
    def __unicode__( self ): return self.name

    def editable( self ):   return getPermission( 'FIN_ACCOUNT_EDIT' )    # TO BE UPDATE
    def deletable( self ): return getPermission( 'FIN_ACCOUNT_DEL' )    # TO BE UPDATE

    @classmethod
    def updateAmount( clz, id, v ):
        try:
            obj = qry( clz ).filter( and_( clz.active == ACTIVE, clz.id == id, ) ).with_lockmode( 'update' ).one()
            obj.amount += v
        except Exception, e:
            raise e
        return obj
